package com.owen.util;

import jxl.SheetSettings;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * excelUtil
 */
public class JxlUtil {

    /**
     * 此方法只能生成表头
     *
     * @param header 表头数据（按表头顺序添加到list传入）
     * @return 生成文件的inputstream
     */
    public static InputStream getExcelHeader(List<String> header) {
        if (header == null || header.size() == 0) {
            throw new RuntimeException("Please input excel header");
        }
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        InputStream is = new ByteArrayInputStream(stream.toByteArray());
        try {
            WritableWorkbook workbook = Workbook.createWorkbook(stream);
            WritableSheet message = workbook.createSheet("工作表", 0);

            SheetSettings ss = message.getSettings();
            ss.setVerticalFreeze(1);//冻结表头
            List<Label> labelList = new ArrayList<Label>();
            // 创建单元格
            for (int i = 0; i < header.size(); i++) {
                labelList.add(new Label(i, 0, header.get(i)));
            }
            // 将单元格添加到sheet页中
            for (Label label : labelList) {
                message.addCell(label);
            }
            // 写入
            workbook.write();
//             关闭工作薄资源
            workbook.close();
            is = new ByteArrayInputStream(stream.toByteArray());
        } catch (IllegalStateException e) {
            System.err.println(e.getMessage());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return is;
    }

    /**
     * 生成表头
     *
     * @param map map最好为有序的LinkedHashMap
     * @return
     */
    public static InputStream getExcelHeader(Map<String, String> map) {
        List<String> header = new ArrayList<String>();
        for (Map.Entry<String, String> entry : map.entrySet()) {
            header.add(entry.getValue());
        }
        return getExcelHeader(header);
    }

    /**
     * 未写入可继续追加数据的excel
     * @param header 表头信息
     * @param stream 数据存放地址
     * @return
     */
    public static WritableWorkbook getExcelHeaderAndWorkbook(List<String> header, ByteArrayOutputStream stream) {
        if (header == null || header.size() == 0) {
            throw new RuntimeException("Please input excel header");
        }
        if (stream == null){
            throw new RuntimeException("Please input ByteArrayOutputStream");
        }
        try {
            WritableWorkbook workbook = Workbook.createWorkbook(stream);
            WritableSheet message = workbook.createSheet("工作表", 0);
            SheetSettings ss = message.getSettings();
            ss.setVerticalFreeze(1);//冻结表头
            List<Label> labelList = new ArrayList<Label>();
            // 创建单元格
            for (int i = 0; i < header.size(); i++) {
                labelList.add(new Label(i, 0, header.get(i)));
            }
            // 将单元格添加到sheet页中
            for (Label label : labelList) {
                message.addCell(label);
            }
            // 不能关闭和写入
//            workbook.write();
//            workbook.close();
            return workbook;
        } catch (IllegalStateException e) {
            System.err.println(e.getMessage());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        throw new RuntimeException("生成文件异常");
    }


    /**
     * 获取表头加数据
     * @param map 推荐使用LinkedHashMap（有序集合），属性名未key，对应表头为值
     * @param dataList 将要导出excel中的数据
     * @return
     * @throws WriteException
     * @throws IOException
     */
    public static InputStream getExcelAndData(Map<String, String> map, List<?> dataList) throws WriteException, IOException, IllegalAccessException {
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        // 表头
        List<String> header = new ArrayList<String>();
        // 对应属性
        List<String> key = new ArrayList<String>();
        for (Map.Entry<String, String> entry : map.entrySet()) {
            header.add(entry.getValue());
            key.add(entry.getKey());
        }
        WritableWorkbook workbook = getExcelHeaderAndWorkbook(header, stream);
        // 获取sheet
        WritableSheet sheet = workbook.getSheet(0);
        int r = 0;

        for (int i = 0; i < dataList.size() ; i++){
            int c = 0;
            r++;
            for (int j = 0; j < key.size() ; j++){
                Object value = getPropertyValue(dataList.get(i), key.get(j));
                String cont = "";
                if (value != null){
                    cont = value.toString();
                }
                sheet.addCell(new Label(c ++,r, cont));
            }

        }
        workbook.write();
        // 关闭工作薄资源
        workbook.close();
        ByteArrayInputStream is = new ByteArrayInputStream(stream.toByteArray());

        return is;
    }

    /**
     * 通过属性名获取属性值
     * @param obj
     * @param propertyName
     * @return
     * @throws IllegalAccessException
     */
    public static Object getPropertyValue(Object obj, String propertyName) throws IllegalAccessException {
        Class<?> Clazz = obj.getClass();
        Field field;
        if ((field = getField(Clazz, propertyName)) == null)
            return null;
        field.setAccessible(true);
        return field.get(obj);
    }

    public static Field getField(Class<?> clazz, String propertyName) {
        if (clazz == null)
            return null;
        try {
            return clazz.getDeclaredField(propertyName);
        } catch (NoSuchFieldException e) {
            return getField(clazz.getSuperclass(), propertyName);
        }
    }



}
